package com.bw.gmall.realtime.utils;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidPooledConnection;
import com.alibaba.fastjson.JSONObject;
import com.google.common.base.CaseFormat;
import org.apache.commons.beanutils.BeanUtils;

import java.lang.reflect.InvocationTargetException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 当前工具类可以适用于任何JDBC方式访问的数据库中的任何查询语句
 * 单行单列：select count(*) from t;
 * 单行多列：select * from t where id='1001'; id为主键
 * 多行单列：select name from t;
 * 多行多列：select * from t;
 * Map<String,List<Object>>
 * List<JSON>
 * List<Map>
 */
public class JdbcUtil {

    public static <T> List<T> queryList(Connection connection, String sql, Class<T> clz, boolean underScoreToCamel)
            throws SQLException, IllegalAccessException, InstantiationException, InvocationTargetException {
        //创建集合用于存放结果数据
        ArrayList<T> result = new ArrayList<>();
        //编译SQL语句
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        //select  *   from   sku_info   where  sku_id=1
        //执行查询
        ResultSet resultSet = preparedStatement.executeQuery();
        //获取查询的元数据信息  所有的字段   所有的字段值
        ResultSetMetaData metaData = resultSet.getMetaData();

        int columnCount = metaData.getColumnCount();
        //遍历结果集,将每行数据转换为T对象并加入集合   行遍历
        while (resultSet.next()) {
            //创建T对象
            T t = clz.newInstance();
            //列遍历,并给T对象赋值
            for (int i = 0; i < columnCount; i++) {
                //获取列名与列值
                String columnName = metaData.getColumnName(i + 1);
                Object value = resultSet.getObject(columnName);
                //tm_id   tm_id   tmId    tim_id  tmId
                //判断是否需要进行下划线与驼峰命名转换
                if (underScoreToCamel) {
                    // CaseFormat.LOWER_UNDERSCORE 这个是下划线命名    to转换成     CaseFormat.LOWER_CAMEL 小驼峰  //tm_id   tmId
                    columnName = CaseFormat.LOWER_UNDERSCORE.to(CaseFormat.LOWER_CAMEL, columnName.toLowerCase());
                }
                //赋值
                //这个方法是封装死了  给对象赋值   JSONObject()     {columnName:value,columnName:value}
                BeanUtils.setProperty(t, columnName, value);
            }
            //将T对象放入集合
            result.add(t);
        }

        resultSet.close();
        preparedStatement.close();

        //返回集合
        return result;
    }

    public static void main(String[] args) throws Exception {

        DruidDataSource dataSource = DruidDSUtil.createDataSource();

        DruidPooledConnection connection = dataSource.getConnection();

        List<JSONObject> list = JdbcUtil.queryList(connection,
                "select  *  from  GMALL_REALTIME.DIM_SKU_INFO where id='"
                        + 22 + "'", JSONObject.class, true);
        System.out.println(list);
        System.out.println(list.get(0).getInteger("tmId"));
        System.out.println(list.get(0).getInteger("category3Id"));
        System.out.println(list.get(0).getInteger("spuId"));

        connection.close();
    }

}
